package org.light.framework.bean;


import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.light.framework.enumeration.IEnum;
import org.light.framework.enumeration.SearchLogic;
import org.light.framework.enumeration.SearchOperator;
import org.light.framework.orm.BelongsInfo;
import org.light.framework.orm.ColumnInfo;
import org.light.framework.orm.DbMapper;
import org.light.framework.util.DateHelper;
import org.light.framework.util.DateStyle;
import org.light.framework.util.ReflectionUtil;
import org.light.framework.util.StringUtil;

/**
 * 查询参数
 * 
 * @author de.cel
 *
 */
public class SearchArgs {

	private StringBuilder builderExt = new StringBuilder();
	private ArrayList<Object> parametersExt = new ArrayList<Object>();

	/**
	 * 构造函数
	 */
	public SearchArgs() {
		this.logic = SearchLogic.Or;
	}

	/**
	 * 构造函数
	 * 
	 * @param keyword
	 *            输入的关键字
	 * @param searchFields
	 *            搜索属性字段列表
	 */
	public SearchArgs(String keyword, ArrayList<String> searchFields) {
		this();
		if (!StringUtil.isNullOrEmpty(keyword)) {
			for (String field : searchFields) {
				fields.put(field, keyword);
			}
		}
	}

	/**
	 * 构造函数
	 * 
	 * @param keyword
	 *            输入的关键字
	 * @param earchFields
	 *            搜索属性字段字典
	 */
	public SearchArgs(String keyword, HashMap<String, String> searchFields) {
		this();
		if (!StringUtil.isNullOrEmpty(keyword)) {
			for (String key : searchFields.keySet()) {
				fields.put(key, keyword);
			}
		}
	}

	private List<String> listFields = new ArrayList<String>(); // 用来存储所有查询的属性项或字段项
	private HashMap<String, Object> fields = new HashMap<String, Object>();
	private ArrayList<SearchExpression> expressions = new ArrayList<SearchExpression>();

	/**
	 * 是否包含Or查询条件
	 */
	protected boolean hasOr() {
		return !fields.isEmpty();
	}

	/**
	 * 是否包含And查询条件
	 */
	protected boolean hasAnd() {
		return !expressions.isEmpty();
	}

	/**
	 * 日期：开始
	 */
	public Date dateStart;

	/**
	 * 日期：结束
	 */
	public Date dateEnd;

	/**
	 * 日期：字段名/属性名
	 */
	public String dateField;

	/**
	 * 查询条件逻辑运算符(AND,OR) 默认为OR
	 *
	 */
	public SearchLogic logic;

	/**
	 * 添加搜索项
	 *
	 * logic field values查询值，可匹配多个（注意，传整型值，如 0 时，会调用四个参数的Add方法）
	 */
	public void add(SearchLogic logic, String field, Object... values) {
		this.add(logic, field, SearchOperator.Eq, values);
	}

	/**
	 * 添加搜索项
	 * 
	 * @param logic
	 *            Or|And
	 * @param field
	 *            查询项
	 * @param optor
	 *            运算符
	 * @param values
	 *            查询值，可匹配多个
	 */
	public void add(SearchLogic logic, String field, SearchOperator optor, Object... values) {

		listFields.add(field);

		if (values == null || optor == SearchOperator.In) {
			SearchExpression expression = new SearchExpression(logic, field, values, optor);
			expressions.add(expression);
		} else {

			for (Object value : values) {
				SearchExpression expression = new SearchExpression(logic, field, value, optor);
				expressions.add(expression);
			}
		}
	}

	/**
	 * 获取所有查询了的字段或属性
	 */
	public List<String> getFields() {

		return listFields;

	}

	/**
	 * 此SearArgs中查询条件对应类的属性名
	 * 
	 * @throws Exception
	 */
	public String toString(Class<?> type) throws Exception {
		StringBuilder builder = new StringBuilder();
		String sql = getSqlString(type);

		if (!StringUtil.isNullOrEmpty(sql))
			builder.append(" WHERE ");

		builder.append(sql);

		return builder.toString();
	}

	private String getSqlString(Class<?> type) throws Exception {
		StringBuilder builder = new StringBuilder();
		String aliasStr = "";

		HashMap<String, ColumnInfo> dicColumns = DbMapper.getColumnsDic(type);
		HashMap<String, BelongsInfo> dicBelongs = DbMapper.getBelongsDic(type);

		if (dicBelongs != null && !dicBelongs.isEmpty()) {
			aliasStr = DbMapper.PRIMARY_TABLE_ALIAS + ".";
		}

		// 获取 OR 查询部分
		builder.append(getOrSql(aliasStr, dicColumns, dicBelongs));

		if (hasOr() && hasAnd())
			builder.append(" AND ");

		// 获取 AND 查询部分
		builder.append(getAndSql(aliasStr, dicColumns, dicBelongs));

		// 获取 扩展 查询部分
		builder.append(builderExt.toString());

		return builder.toString();
	}

	/**
	 * 此SearchArgs中查询条件对应数据库中的列名
	 */
	public String toString() {
		StringBuilder builder = new StringBuilder();
		String sql = getSqlString();

		if (!StringUtil.isNullOrEmpty(sql))
			builder.append(" WHERE ");

		builder.append(sql);

		return builder.toString();
	}

	private String getSqlString() {
		StringBuilder builder = new StringBuilder();

		// 获取 OR 查询部分
		builder.append(getOrSql());

		if (hasOr() && hasAnd())
			builder.append(" AND ");

		// 获取 AND 查询部分
		builder.append(getAndSql());

		// 获取 扩展 查询部分
		builder.append(builderExt.toString());

		return builder.toString();
	}

	/**
	 * 获取对象查询参数
	 * 
	 * @throws Exception
	 *
	 */
	public ArrayList<Object> getDataParameters(Class<?> type) throws Exception {
		ArrayList<Object> list = new ArrayList<Object>();
		HashMap<String, BelongsInfo> dicBelongs = DbMapper.getBelongsDic(type);

		// int idx_f = 0;

		for (String pName : fields.keySet()) {
			// idx_f++;

			// String name = formatParameterName(pName, idx_f);
			Object value = fields.get(pName);

			if (value == null)// 空值
				list.add(null);
			else if (dicBelongs.containsKey(pName) && dicBelongs.get(pName).getClass() == value.getClass()) // 查询值是一个实体，取出实体对应的主键值
			{
				ColumnInfo primaryKey = DbMapper.getPrimaryKey(value.getClass());
				Object pkValue = value.getClass().getField(primaryKey.propertyName).get(value);
				list.add(pkValue);
			} else if (fields.get(pName) instanceof Enum) // 枚举值
				list.add((int) value);
			else if (fields.get(pName) instanceof Boolean) // 布尔值
				list.add((boolean) value ? 1 : 0);
			else if (DateHelper.isDate(fields.get(pName)) || fields.get(pName) instanceof Collection) // 时间或集合
				continue;
			else
				list.add("%" + value + "%");
		}

		// int idx_e = 0;

		for (SearchExpression se : expressions) {
			// idx_e++;

			Object value = se.propertyValue;

			if (value == null) // 空值
				value = null;
			else if (dicBelongs.containsKey(se.propertyName)
					&& dicBelongs.get(se.propertyName).getClass() == value.getClass()) {
				ColumnInfo primaryKey = DbMapper.getPrimaryKey(value.getClass());
				value = ReflectionUtil.getPropertyValue(value, primaryKey.propertyName);
			} else if (se.propertyValue instanceof IEnum)
				value = ((IEnum) se.propertyValue).getId();
			else if (se.propertyValue instanceof Boolean)
				value = (boolean) se.propertyValue ? 1 : 0;
			else if (se.propertyValue instanceof Collection)
				continue;
			else if (se.operator == SearchOperator.InSql)
				continue;

			list.add(value);
		}

		if (!this.parametersExt.isEmpty())
			list.addAll(this.parametersExt);

		return list;
	}

	/**
	 * 获取数据表查询参数
	 */
	public ArrayList<Object> getDataParameters() {
		ArrayList<Object> list = new ArrayList<Object>();

		// int idx_f = 0;
		for (String pName : fields.keySet()) {
			// idx_f++;

			Object value = fields.get(pName);
			if (value == null)
				list.add(null);
			else if (StringUtil.isNullOrEmpty(value))
				list.add("");
			else if (DateHelper.isDate(value) || value instanceof Collection) // 日期或集合
																				// 不参数化
				continue;
			else
				list.add("%" + value + "%");
		}

		// int idx_e = 0;
		for (SearchExpression se : expressions) {
			// idx_e++;

			if (se.propertyValue == null)
				continue;
			else if (se.propertyValue instanceof Collection)
				continue;
			else if (se.operator == SearchOperator.InSql)
				continue;
			else if (se.propertyValue instanceof IEnum)
				se.propertyValue = ((IEnum) se.propertyValue).getId();
			else if (se.propertyValue instanceof Boolean)
				se.propertyValue = (int) se.propertyValue;

			list.add(se.propertyValue);
		}

		if (!this.parametersExt.isEmpty())
			list.addAll(this.parametersExt);

		return list;
	}

	/**
	 * 清空查询项
	 */
	public void clear() {
		this.fields.clear();
		this.expressions.clear();
	}

	private String getOrSql(String aliasStr, HashMap<String, ColumnInfo> dicColumns,
			HashMap<String, BelongsInfo> dicBelongs) throws Exception {
		StringBuilder builderOr = new StringBuilder();

		// OR
		if (hasOr())
			builderOr.append("(");

		// int idx_f = 0;
		for (String propertyName : fields.keySet()) {
			// idx_f++;

			// String parameterName = formatParameterName(propertyName, idx_f);
			Object value = fields.get(propertyName);

			String columnAlias = "";

			if (dicColumns.containsKey(propertyName)) {
				columnAlias = aliasStr + dicColumns.get(propertyName).name;

				if (DateHelper.isDate(value)) {
					Date input = DateHelper.stringToDate(value);
					String start = DateHelper.getStartOfDay(input).toString();
					String end = DateHelper.getEndOfDay(input).toString();

					builderOr.append(" " + columnAlias + " BETWEEN '" + start + "' AND '" + end + "'");
				} else if (value instanceof Enum)
					// builderOr.append(" " + columnAlias + " = @" +
					// propertyName);
					builderOr.append(" " + columnAlias + " =? ");
				else if (value == null)
					builderOr.append(" " + columnAlias + " IS NULL");
				else
					builderOr.append(" " + columnAlias + " LIKE ?");
				// builderOr.append(" " + columnAlias + " LIKE @" +
				// parameterName);
			} else if (dicBelongs.containsKey(propertyName)) {
				BelongsInfo bc = dicBelongs.get(propertyName); // 找到对应的外键信息
				columnAlias = DbMapper.PRIMARY_TABLE_ALIAS + "." + bc.name;

				if (value == null)
					builderOr.append(" " + columnAlias + " IS NULL");
				else
					// builderOr.append(" " + columnAlias + " LIKE @" +
					// parameterName);
					builderOr.append(" " + columnAlias + " LIKE ?");
			} else if (propertyName.contains(".")) {
				String[] temp = propertyName.split("\\."); // Aaa.Bbb.Ccc =>
															// Aaa.Bbb、Ccc
				String lastPName = temp[temp.length - 1];// Ccc
				String firstPName = propertyName.substring(0, propertyName.lastIndexOf(".")); // Aaa.Bbb

				BelongsInfo bc = dicBelongs.get(firstPName); // 找到对应的外键信息（表别名）

				// 找到外键表的列
				int cascade = propertyName.split("\\.").length;
				ArrayList<ColumnInfo> bcColumns = DbMapper.getColumns(bc.entityType, cascade);
				ColumnInfo bcColumn = ColumnInfo.findByPropertyName(bcColumns, lastPName);
				if (bcColumn != null) {
					columnAlias = bc.alias + "." + bcColumn.name;

					if (value == null)
						builderOr.append(" " + columnAlias + " IS NULL");
					else if (DateHelper.isDate(value)) {
						Date input = DateHelper.stringToDate(value);
						String start = DateHelper.getStartOfDay(input).toString();
						String end = DateHelper.getEndOfDay(input, DateStyle.YYYY_MM_DD);

						builderOr.append(" " + columnAlias + " BETWEEN '" + start + "' AND '" + end + "'");
					} else
						// builderOr.append(" " + columnAlias + " LIKE @" +
						// parameterName);
						builderOr.append(" " + columnAlias + " LIKE ?");
				} else
					throw new Exception("不支持按 " + propertyName + " 查询");
			} else {
				throw new Exception(" 不支持按 " + propertyName + " 查询，请检查属性是否存在");
			}
			builderOr.append(" OR ");
		}

		if (hasOr()) {
			builderOr.delete(builderOr.length() - 4, builderOr.length() - 1);
			builderOr.append(")");
		}

		return builderOr.toString();
	}

	private String getAndSql(String aliasStr, HashMap<String, ColumnInfo> dicColumns,
			HashMap<String, BelongsInfo> dicBelongs) throws Exception {
		StringBuilder builderAnd = new StringBuilder();

		for (SearchExpression se : expressions) {

			if (dicColumns.containsKey(se.propertyName)) {
				builderAnd.append(aliasStr + dicColumns.get(se.propertyName).name);
			} else if (dicBelongs.containsKey(se.propertyName)) {
				BelongsInfo bc = dicBelongs.get(se.propertyName); // 找到对应的外键信息
				builderAnd.append(DbMapper.PRIMARY_TABLE_ALIAS + "." + bc.name);
			} else if (se.propertyName.contains(".")) {
				String[] temp = se.propertyName.split("\\."); // Aaa.Bbb.Ccc =>
																// Aaa.Bbb、Ccc
				String lastPName = temp[temp.length - 1]; // Ccc
				String firstPName = se.propertyName.substring(0, se.propertyName.lastIndexOf(".")); // Aaa.Bbb

				BelongsInfo bc = dicBelongs.get(firstPName); // 找到对应的外键信息（表别名）

				// 找到外键表的列
				int cascade = se.propertyName.split("\\.").length;
				ArrayList<ColumnInfo> bcColumns = DbMapper.getColumns(bc.entityType, cascade);
				ColumnInfo bcColumn = ColumnInfo.findByPropertyName(bcColumns, lastPName);
				if (bcColumn != null)
					builderAnd.append(bc.alias + "." + bcColumn.name);
				else
					throw new Exception("不支持按 " + se.propertyName + " 查询");
			} else {
				continue;
			}
			if (se.operator == SearchOperator.Ge)
				builderAnd.append(">=");
			else if (se.operator == SearchOperator.Gt)
				builderAnd.append(">");
			else if (se.operator == SearchOperator.Le)
				builderAnd.append("<=");
			else if (se.operator == SearchOperator.Lt)
				builderAnd.append("<");
			else if (se.operator == SearchOperator.Not && se.propertyValue != null)
				builderAnd.append("<>");
			else if (se.operator == SearchOperator.Like)
				builderAnd.append(" LIKE ");
			else if (se.propertyValue == null) {
				if (se.operator == SearchOperator.Not)
					builderAnd.append(" IS NOT NULL ");
				else
					builderAnd.append(" IS NULL ");
			} else if (se.operator == SearchOperator.In && se.propertyValue instanceof Collection) {
				builderAnd.append(" IN (");

				Collection<?> values = (Collection<?>) se.propertyValue;
				for (Object item : values) {
					if (item instanceof Enum || item instanceof Integer)
						builderAnd.append((int) item + ",");
					else if (item instanceof Double)
						builderAnd.append((double) item + ",");
					else if (item instanceof Float)
						builderAnd.append((float) item + ",");
					else if (item instanceof Boolean)
						builderAnd.append((((boolean) item) ? 1 : 0) + ",");
					else
						builderAnd.append("'" + item + "',");
				}
				builderAnd.deleteCharAt(builderAnd.length() - 1);
				builderAnd.append(" ) ");
			} else if (se.operator == SearchOperator.InSql) {
				builderAnd.append(" IN (");
				builderAnd.append(se.propertyValue);
				builderAnd.append(" ) ");
			} else
				builderAnd.append("=");

			if (se.operator != SearchOperator.InSql && se.propertyValue != null
					&& !(se.propertyValue instanceof Collection))
				builderAnd.append("?");
			if (se.logic == SearchLogic.Or)
				builderAnd.append(" OR  ");
			else
				builderAnd.append(" AND ");

		}
		if (hasAnd()) {
			builderAnd.delete(builderAnd.length() - 4, builderAnd.length());
		}
		return builderAnd.toString();
	}

	private String getOrSql() {
		StringBuilder builderOr = new StringBuilder();

		// OR
		if (hasOr())
			builderOr.append(" (");
		// int idx_f = 0;
		for (String column : fields.keySet()) {
			// idx_f++;
			// String parameterName = formatParameterName(column, idx_f);
			Object value = fields.get(column);
			if (DateHelper.isDate(value)) {
				Date input = DateHelper.stringToDate(value);
				String start = DateHelper.getStartOfDay(input).toString();
				String end = DateHelper.getEndOfDay(input).toString();

				builderOr.append(column + " BETWEEN '" + start + "' AND '" + end + "'");
			} else if (value == null)
				builderOr.append(" " + column + " IS NULL");
			else
				// builderOr.append(column + " LIKE @" + parameterName);
				builderOr.append(column + " LIKE ?");

			builderOr.append(" OR ");
		}

		if (hasOr()) {
			builderOr.delete(builderOr.length() - 3, builderOr.length());
			builderOr.append(")");
		}

		return builderOr.toString();
	}

	private String getAndSql() {
		StringBuilder builderAnd = new StringBuilder();
		// AND
		// int idx_e = 0;
		for (SearchExpression se : expressions) {
			// idx_e++;
			// builder2.append("(");
			builderAnd.append(" " + se.propertyName);
			if (se.operator == SearchOperator.Ge)
				builderAnd.append(">=");
			else if (se.operator == SearchOperator.Gt)
				builderAnd.append(">");
			else if (se.operator == SearchOperator.Le)
				builderAnd.append("<=");
			else if (se.operator == SearchOperator.Lt)
				builderAnd.append("<");
			else if (se.operator == SearchOperator.Not && se.propertyValue != null)
				builderAnd.append("<>");
			else if (se.operator == SearchOperator.Like)
				builderAnd.append(" LIKE ");
			else if (se.propertyValue == null) {
				if (se.operator == SearchOperator.Not)
					builderAnd.append(" IS NOT NULL ");
				else
					builderAnd.append(" IS NULL ");
			} else if (se.operator == SearchOperator.In && se.propertyValue instanceof Collection) {
				builderAnd.append(" IN (");

				Collection<?> values = (Collection<?>) se.propertyValue;
				for (Object item : values) {
					if (item instanceof Enum || item instanceof Integer)
						builderAnd.append((int) item + ",");
					else if (item instanceof Double)
						builderAnd.append((double) item + ",");
					else if (item instanceof Float)
						builderAnd.append((float) item + ",");
					else if (item instanceof Boolean)
						builderAnd.append((((boolean) item) ? 1 : 0) + ",");
					else
						builderAnd.append("'" + item + "',");
				}
				builderAnd.deleteCharAt(builderAnd.length() - 1);
				builderAnd.append(" ) ");
			} else if (se.operator == SearchOperator.InSql) {
				builderAnd.append(" IN (");
				builderAnd.append(se.propertyValue);
				builderAnd.append(" ) ");
			} else
				builderAnd.append("=");

			if (se.propertyValue != null && !(se.propertyValue instanceof Collection)
					&& se.operator != SearchOperator.InSql)
				builderAnd.append("?");
			if (se.logic == SearchLogic.Or)
				builderAnd.append(" OR ");
			else
				builderAnd.append(" AND");

		}
		if (hasAnd()) {
			builderAnd.delete(builderAnd.length() - 3, builderAnd.length());
		}

		return builderAnd.toString();
	}

	/**
	 * OR 逻辑运算(查询表或视图用 )
	 * 
	 * @param args
	 * @return
	 */
	public SearchArgs or(SearchArgs args) {
		if (this.hasAnd() || this.hasOr() || this.builderExt.length() > 0)
			this.builderExt.append(" OR ");
		this.builderExt.append("(");
		this.builderExt.append(args.getSqlString());
		this.builderExt.append(")");

		// 追加Sql参数
		ArrayList<Object> sqlparams = args.getDataParameters();

		for (Object p : sqlparams) {
			if (!this.parametersExt.contains(p))
				this.parametersExt.add(p);
		}

		return this;
	}

	/**
	 * AND 逻辑运算(查询表或视图用 )
	 * 
	 * @param args
	 * @return
	 */
	public SearchArgs and(SearchArgs args) {
		if (this.hasAnd() || this.hasOr() || this.builderExt.length() > 0)
			this.builderExt.append(" AND ");
		this.builderExt.append("(");
		this.builderExt.append(args.getSqlString());
		this.builderExt.append(")");

		// 追加Sql参数
		ArrayList<Object> sqlparams = args.getDataParameters();

		for (Object p : sqlparams) {
			if (!this.parametersExt.contains(p))
				this.parametersExt.add(p);
		}

		return this;
	}

	/**
	 * OR 逻辑运算(查询对象用 )
	 * 
	 * @throws Exception
	 */
	public SearchArgs or(Class<?> type, SearchArgs args) throws Exception {

		// 追加SQL语句
		if (this.hasAnd() || this.hasOr() || this.builderExt.length() > 0)
			this.builderExt.append(" OR ");
		this.builderExt.append("(");
		String newString = args.getSqlString(type);

		// 追加Sql参数
		ArrayList<Object> sqlparams = args.getDataParameters(type);

		for (Object p : sqlparams) {
			this.parametersExt.add(p);
		}
		this.builderExt.append(newString);
		this.builderExt.append(")");

		return this;
	}

	/**
	 * AND 逻辑运算(查询对象用 )
	 * 
	 * @throws Exception
	 */
	public SearchArgs and(Class<?> type, SearchArgs args) throws Exception {
		if (this.hasAnd() || this.hasOr() || this.builderExt.length() > 0)
			this.builderExt.append(" AND ");
		this.builderExt.append("(");

		String newString = args.getSqlString(type);

		ArrayList<Object> sqlparams = args.getDataParameters(type);

		for (Object obj : sqlparams) {
			this.parametersExt.add(obj);
		}

		this.builderExt.append(newString);
		this.builderExt.append(")");

		return this;
	}
}
